<?php

namespace app\modules\admin\controllers;
error_reporting(0);
use app\models\ActiveMqApi;
use app\models\Agent;
use app\models\Api;
use app\models\Card;
use app\models\CardAttr;
use app\models\CardAttrValue;
use app\models\CardBill;
use app\models\CardDatalog;
use app\models\CardDispatchForm;
use app\models\CardImportForm;
use app\models\CardSetPackageForm;
use app\models\CardSetTermForm;
use app\models\CardStatusChange;
use app\models\CardHistory;
use app\models\Isp;
use app\models\KeyMap;
use app\models\ManagerLog;
use app\models\Cardtype;
use app\models\Package;
use app\models\PackageIsp;
use app\models\Task;
use app\models\User;
use app\models\UserBill;
use app\models\UserPackage;
use app\models\AgentProfit;
use app\models\CorrectCardRecharge;
use app\models\UserCardRecharge;
use Exception;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use Yii;
use yii\base\InvalidConfigException;
use yii\data\Pagination;
use yii\db\Expression;
use yii\helpers\ArrayHelper;
use yii\helpers\Url;
use yii\web\BadRequestHttpException;
use yii\web\ForbiddenHttpException;
use yii\web\NotFoundHttpException;

/**
 * 物联网卡管理
 * Class CardController
 * @package app\modules\admin\controllers
 */
class CardController extends BaseController
{
    /**
     * 接口列表
     * @return string
     * @throws ForbiddenHttpException
     */
    public function actionApiList()
    {
        if (!$this->manager->can('card/api')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $query = Api::find();
        $apiList = $query->all();
        return $this->render('api_list', [
            'apiList' => $apiList,
        ]);
    }

    /**
     * 添加修改接口
     * @return string
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionApiEdit()
    {
        if (!$this->manager->can('card/api')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        if (!empty($id)) {
            $api = Api::findOne(['id' => $id]);
            if (empty($api)) {
                throw new NotFoundHttpException('没有找到接口信息。');
            }
        } else {
            $api = new Api();
        }
        if ($api->load($this->post()) && $api->save()) {
            ManagerLog::info($this->manager->id, '保存接口', print_r($api->attributes, true));
            Yii::$app->session->addFlash('success', '数据已保存。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/api-list']),
                'txt' => '接口列表'
            ]));
        }
        return $this->render('api_edit', [
            'api' => $api,
        ]);
    }

    /**
     * 运营商列表
     * @return string
     * @throws ForbiddenHttpException
     */
    public function actionIspList()
    {
        if (!$this->manager->can('card/isp')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $query = Isp::find();
        $query->andWhere(['status' => Isp::STATUS_OK]);
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $query->offset($pagination->offset)->limit($pagination->limit);
        $query->orderBy('id DESC');
        $ispList = $query->all();
        return $this->render('isp_list', [
            'ispList' => $ispList,
            'pagination' => $pagination,
        ]);
    }

    /**
     * 运营商详情
     * @return string
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionIspView()
    {
        if (!$this->manager->can('card/isp')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        $isp = Isp::findOne(['id' => $id]);
        if (empty($isp)) {
            throw new NotFoundHttpException('没有找到运营商。');
        }
        return $this->render('isp_view', [
            'isp' => $isp,
        ]);
    }

    /**
     * 同步运营商所有卡信息
     * @return array
     */
    public function actionSyncIspCard()
    {
        if (!$this->manager->can('card/isp')) {
            return ['error' => 'SERVER', 'message' => '没有权限。'];
        }
        $id = $this->get('id');
        $isp = Isp::findOne(['id' => $id]);
        if (empty($isp)) {
            return ['error' => 'SERVER', 'message' => '没有找到运营商。'];
        }
        (new ActiveMqApi('/queue/card'))->send([
            'action' => 'isp/sync-card',
            'isp_id' => $isp->id,
        ]);
        return [];
    }

    /**
     * 添加修改运营商
     * @return string
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionIspEdit()
    {
        if (!$this->manager->can('card/isp')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        if (!empty($id)) {
            $isp = Isp::findOne(['id' => $id]);
            if (empty($isp)) {
                throw new NotFoundHttpException('没有找到运营商信息。');
            }
        } else {
            $isp = new Isp();
            $apiId = $this->get('api_id');
            $api = Api::findOne(['id' => $apiId]);
            if (empty($api)) {
                throw new NotFoundHttpException('没有找到接口信息。');
            }
            $isp->api_id = $api->id;
            $isp->create_time = time();
            $isp->status = Isp::STATUS_OK;
        }
        if ($isp->load($this->post())) {
            $postData = $this->post('data');
            if (!empty($postData)) {
                $isp->data = json_encode($postData);
            }
            if ($isp->save()) {
                ManagerLog::info($this->manager->id, '保存运营商', print_r($isp->attributes, true));
                Yii::$app->session->addFlash('success', '数据已保存。');
                Yii::$app->session->setFlash('redirect', json_encode([
                    'url' => Url::to(['/admin/card/isp-list']),
                    'txt' => '运营商列表'
                ]));
            }
        }
        return $this->render('isp_edit', [
            'isp' => $isp,
        ]);
    }

    /**
     * 运营商分析统计
     * @return string
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionIspAnalyze()
    {
        if (!$this->manager->can('card/isp')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        $isp = Isp::findOne(['id' => $id]);
        if (empty($isp)) {
            throw new NotFoundHttpException('没有找到运营商信息。');
        }
        // 总数量
        $amount = Card::find()->andWhere(['isp_id' => $isp->id])->count();
        // 状态统计
        $statusCount = Card::find()
            ->asArray()
            ->select([
                'status' => 'status',
                'amount' => new Expression('COUNT(0)'),
            ])
            ->andWhere(['isp_id' => $isp->id])
            ->groupBy('status')
            ->all();
        array_walk($statusCount, function (&$item) {
            $item['status'] = KeyMap::getValue('card_status', $item['status']);
        });
        $statusCount = ArrayHelper::map($statusCount, 'status', 'amount');
        return $this->render('isp_analyze', [
            'isp' => $isp,
            'amount' => $amount,
            'statusCount' => $statusCount,
        ]);
    }

    /**
     * 删除运营商AJAX接口
     * @return array
     */
    public function actionDeleteIsp()
    {
        if (!$this->manager->can('card/isp')) {
            return ['error' => 'SERVER', 'message' => '没有权限。'];
        }
        $id = $this->get('id');
        $isp = Isp::findOne(['id' => $id, 'status' => Isp::STATUS_OK]);
        if (empty($isp)) {
            return ['error' => 'SERVER', 'message' => '没有找到运营商。'];
        }
        if (Card::find()
            ->andWhere(['isp_id' => $isp->id])
            ->andWhere('uid > 0')
            ->exists()) {
            return ['error' => 'SERVER', 'message' => '存在已经分配给用户的卡，请先回收卡后再删除。'];
        }
        $isp->status = Isp::STATUS_DELETE;
        $isp->save(false);
        ManagerLog::info($this->manager->id, '删除运营商。', print_r($isp->attributes, true));
        return ['result' => 'success'];
    }

    /**
     * 套餐列表
     * @return string
     * @throws ForbiddenHttpException
     */
    public function actionPackageList()
    {
        if (!$this->manager->can('card/package')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $query = Package::find();
        $query->orderBy('id DESC');
        $packageList = $query->all();
        return $this->render('package_list', [
            'packageList' => $packageList,
        ]);
    }
    
    /**
     * ISP套餐列表
     * @return string
     * @throws ForbiddenHttpException
     */
    public function actionPackageIspList()
    {
        if (!$this->manager->can('card/package-isp')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $query = PackageIsp::find();
        $query->orderBy('id DESC');
        $packageispList = $query->all();
        return $this->render('package_isp_list', [
            'packageispList' => $packageispList,
        ]);
    }

    /**
     * 套餐详情
     * @return string
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionPackageView()
    {
        if (!$this->manager->can('card/package')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        $package = Package::findOne(['id' => $id]);
        if (empty($package)) {
            throw new NotFoundHttpException('没有找到套餐。');
        }
        return $this->render('package_view', [
            'package' => $package,
        ]);
    }
    
    /**
     * ISP套餐详情
     * @return string
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionPackageIspView()
    {
        if (!$this->manager->can('card/package-isp')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        $packageisp = PackageIsp::findOne(['id' => $id]);
        if (empty($packageisp)) {
            throw new NotFoundHttpException('没有找到套餐。');
        }
        return $this->render('package_isp_view', [
            'packageisp' => $packageisp,
        ]);
    }

    /**
     * 添加修改套餐
     * @return string
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionPackageEdit()
    {
        if (!$this->manager->can('card/package')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        if (!empty($id)) {
            $package = Package::findOne(['id' => $id]);
            if (empty($package)) {
                throw new NotFoundHttpException('没有找到套餐。');
            }
        } else {
            $package = new Package();
            $package->create_time = time();
        }
        if ($package->load($this->post()) && $package->save()) {
            ManagerLog::info($this->manager->id, '保存套餐', print_r($package->attributes, true));
            Yii::$app->session->addFlash('success', '数据已保存。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/package-list']),
                'txt' => '套餐列表'
            ]));
        }
        return $this->render('package_edit', [
            'package' => $package,
        ]);
    }
    //配置套餐是否可以在新增代理商时写入代理商套餐表
    public function actionPackageConfig(){
        if(!$this->manager->can('user/user-package')){
            throw new Exception("没有权限");    
        }
        
        $id = $this->get('id');
        $model = Yii::$app->request->post('Package');
        $pack_config = Package::findOne(['id'=>$id]);
        if(empty($id)){
            throw new NotFoundHttpException('没有找到套餐');
        }
        if(empty($model)){
            $pack_config->exception = $pack_config->exception;
        }else{
            $pack_config->exception = $model['exception'];
        }
        
        if($pack_config->load($this->post()) && $pack_config->save()){
            ManagerLog::info($this->manager->id,'配置套餐',print_r($pack_config->attributes,true));
            Yii::$app->session->addFlash('success','数据已保存');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/package-list']),
                'txt' => '套餐列表'
            ]));
        }
        return $this->render('package_config', [
            'pack_config' => $pack_config,
        ]);
    }
    /**
     * 添加修改ISP套餐
     * @return string
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionPackageIspEdit()
    {
        if (!$this->manager->can('card/package-isp')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        if (!empty($id)) {
            $packageisp = PackageIsp::findOne(['id' => $id]);
            if (empty($packageisp)) {
                throw new NotFoundHttpException('没有找到套餐。');
            }
        } else {
            $packageisp = new PackageIsp();
            $packageisp->create_time = time();
        }
        if ($packageisp->load($this->post()) && $packageisp->save()) {
            ManagerLog::info($this->manager->id, '保存ISP套餐', print_r($packageisp->attributes, true));
            Yii::$app->session->addFlash('success', '数据已保存。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/package-isp-list']),
                'txt' => '套餐列表'
            ]));
        }
        return $this->render('package_isp_edit', [
            'packageisp' => $packageisp,
        ]);
    }

    /**
     * 卡列表
     * @return string
     * @throws ForbiddenHttpException
     * @throws BadRequestHttpException
     * @throws NotFoundHttpException
     */
    public function actionList()
    {
        if (!$this->manager->can('card/list')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $query = Card::find()->alias('card');
        $query->joinWith('isp isp');
        $query->andWhere(['isp.status' => Isp::STATUS_OK]);
        if (!empty($this->get('search_agent'))) {
            /** @var Agent $searchAgent */
            $searchAgent = Agent::find()
                ->andWhere(['username' => $this->get('search_agent')])
                ->one();
            if (empty($searchAgent)) {
                throw new NotFoundHttpException('没有找到代理商【' . $this->get('search_agent') . '】。');
            } else {
                $agentIdList = [$searchAgent->id];
                if ($this->get('search_sub_agent') == 1) { // 包含下级代理商
                    $agentIdList = array_merge($agentIdList, $searchAgent->getSubAidList());
                }
                $query->andWhere(['agent_id' => $agentIdList]);
            }
        }
        if (!empty($this->get('search_username'))) {
            $query->joinWith('user user')->andWhere(['user.username' => $this->get('search_username')]);
        }
        $query->andFilterWhere(['card.status' => $this->get('search_status')]);
        //$query->andFilterWhere(['card.iccid' => $this->get('search_iccid')]);
        $query->andFilterWhere(['like', 'card.iccid', $this->get('search_iccid').'%', false]);
        $query->andFilterWhere(['card.msisdn' => $this->get('search_msisdn')]);
        $query->andFilterWhere(['card.batch_no' => $this->get('search_batch_no')]);
        $query->andFilterWhere(['card.api_id' => $this->get('search_api_id')]);
        $query->andFilterWhere(['card.isp_id' => $this->get('search_isp_id')]);
        $query->andFilterWhere(['card.package_id' => $this->get('search_package_id')]);
        if (!empty($this->get('search_dispatch_time'))) {
            $time = strtotime($this->get('search_dispatch_time'));
            $query->andWhere(['>=', 'dispatch_time', $time]);
            $query->andWhere(['<', 'dispatch_time', $time + 86400]);
        }
        $searchEndDayAlert = $this->get('search_end_day_alert');
        if (!empty($searchEndDayAlert)) { // 到期提醒的卡
            $query->andWhere(['>', 'card.term_end_time', 0]);
            $query->andWhere(['<=', 'card.term_end_time', time() + $searchEndDayAlert * 86400]);
        }
        $searchOverload = $this->get('search_overload', 0) == 1;
        if ($searchOverload) {
            $query->joinWith('package package');
            $query->andWhere(new Expression('card.data > card.total_flow'));
        }
        $searchValidity = $this->get('search_validity',0) == 1;
        if($searchValidity){
            $query->andFilterWhere(['>=','term_end_time',0]);
            $query->andFilterWhere(['>','term_end_time',time()]);
        }
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $sortName = $this->get('sort_name', 'card.refresh_time');
        $sortOrder = $this->get('sort_order', 'DESC');
        if (!empty($sortName) && !in_array($sortName, ['card.id', 'card.iccid', 'card.msisdn', 'card.dispatch_time', 'card.refresh_time', 'card.data', 'card.term_end_time'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        if (!empty($sortOrder) && !in_array($sortOrder, ['ASC', 'DESC'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        $query->orderBy([
            $sortName => ['ASC' => SORT_ASC, 'DESC' => SORT_DESC][$sortOrder],
        ]);
        if ($this->get('export') == 'excel') { // 导出Excel文件
            try {
                $excel = new Spreadsheet();
                $sheet = $excel->setActiveSheetIndex(0);
                foreach ([
                             '编号',
                             '代理商编号',
                             '代理商名',
                             '用户编号',
                             '用户名',
                             '套餐id',
                             '套餐',
                             'ICCID',
                             'MSISDN',
                             'IMSI',
                             '状态',
                             '分配时间',
                             '激活时间',
                             '开关机',
                             '数据用量（本周期）',
                             '总流量',
                             '运营商流量',
                             '套餐有效期',
                             '套餐结束时间',
                         ] as $index => $title) {
                    $sheet->setCellValue(chr(65 + $index) . '1', $title);
                }
                $sheet->getStyle('A1:Z1')->applyFromArray(['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]]);
                $r = 2;
                /** @var Card $card */
                foreach ($query->each() as $card) {
                    $sheet->setCellValueExplicitByColumnAndRow(1, $r, $card->id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(2, $r, $card->agent_id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(3, $r, empty($card->agent_id) ? '' : $card->agent->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, $r, $card->uid, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(5, $r, empty($card->uid) ? '' : $card->user->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(6, $r, $card->package_id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(7, $r, empty($card->package_id) ? '' : $card->package->name, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(8, $r, $card->iccid, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(9, $r, $card->msisdn, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(10, $r, $card->imsi, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(11, $r, KeyMap::getValue('card_status', $card->status), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(12, $r, Yii::$app->formatter->asDatetime($card->dispatch_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(13, $r, Yii::$app->formatter->asDatetime($card->active_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(14, $r, $card->on_off, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(15, $r, Yii::$app->formatter->asShortSize($card->data), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(16, $r, Yii::$app->formatter->asShortSize($card->total_flow), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(17, $r, Yii::$app->formatter->asShortSize($card->data_isp), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(18, $r, $card->term_length . KeyMap::getValue('card_term_length_unit', $card->term_length_unit), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(19, $r, Yii::$app->formatter->asDatetime($card->term_end_time), DataType::TYPE_STRING);
                    $r++;
                }
                $sheet->freezePane('A2');
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.xls"');
                header('Cache-Control: max-age=0');
                $excelWriter = IOFactory::createWriter($excel, 'Xls');
                $excelWriter->save('php://output');
            } catch (Exception $e) {
                Yii::error('导出Excel文件时出错：' . $e->getMessage());
                Yii::error($e->getTraceAsString());
                echo $e->getMessage();
            }
            die();
        }
        if ($this->get('export') == 'csv') { // 导出CSV文件
            header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
            header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.csv"');
            header('Cache-Control: max-age=0');
            echo "编号,代理商编号,用户编号,套餐,ICCID,MSISDN,IMSI,状态,分配时间,激活时间,开关机,数据用量（本月）（B）,套餐有效期,套餐结束时间\r\n";
            /** @var Card $card */
            foreach ($query->each() as $card) {
                echo $card->id, ',';
                echo $card->agent_id, ',';
                echo $card->uid, ',';
                echo empty($card->package_id) ? '' : $card->package->name, ',';
                echo $card->iccid, ',';
                echo $card->msisdn, ',';
                echo $card->imsi, ',';
                echo KeyMap::getValue('card_status', $card->status), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->dispatch_time), ',';
                } catch (InvalidConfigException $e) {
                    echo ',';
                }
                echo $card->on_off, ',';
                echo $card->data, ',';
                echo $card->term_length, KeyMap::getValue('card_term_length_unit', $card->term_length_unit), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->term_end_time);
                } catch (InvalidConfigException $e) {
                }
                echo "\r\n";
            }
            die();
        }
        $query->offset($pagination->offset)->limit($pagination->limit);
        $cardList = $query->all();
        $flow = '0';
        return $this->render('card_list', [
            'cardList' => $cardList,
            'flow' => $flow,
            'pagination' => $pagination,
        ]);
    }
    /**
     * 超出流量卡
     */
    public function actionCardListOverload()
    {
        if (!$this->manager->can('card/card-list-overload')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $query = Card::find()->alias('card');
        $query->joinWith('isp isp');
        $query->andWhere(['isp.status' => Isp::STATUS_OK]);
        $search_ratio = $this->get('search_ratio');
        if (!empty($this->get('search_agent'))) {
            /** @var Agent $searchAgent */
            $searchAgent = Agent::find()
                ->andWhere(['username' => $this->get('search_agent')])
                ->one();
            if (empty($searchAgent)) {
                throw new NotFoundHttpException('没有找到代理商【' . $this->get('search_agent') . '】。');
            } else {
                $agentIdList = [$searchAgent->id];
                if ($this->get('search_sub_agent') == 1) { // 包含下级代理商
                    $agentIdList = array_merge($agentIdList, $searchAgent->getSubAidList());
                }
                $query->andWhere(['agent_id' => $agentIdList]);
            }
        }
        if (!empty($this->get('search_username'))) {
            $query->joinWith('user user')->andWhere(['user.username' => $this->get('search_username')]);
        }
        
        $query->andFilterWhere(['card.status' => $this->get('search_status')]);
        //$query->andFilterWhere(['card.iccid' => $this->get('search_iccid')]);
        $query->andFilterWhere(['like', 'card.iccid', $this->get('search_iccid').'%', false]);
        $query->andFilterWhere(['card.msisdn' => $this->get('search_msisdn')]);
        $query->andFilterWhere(['card.batch_no' => $this->get('search_batch_no')]);
        $query->andFilterWhere(['card.api_id' => $this->get('search_api_id')]);
        $query->andFilterWhere(['card.isp_id' => $this->get('search_isp_id')]);
        $query->andFilterWhere(['card.package_id' => $this->get('search_package_id')]);
        $query->andFilterWhere(['<>','card.package_id','null']);
        if (!empty($this->get('search_dispatch_time'))) {
            $time = strtotime($this->get('search_dispatch_time'));
            $query->andWhere(['>=', 'dispatch_time', $time]);
            $query->andWhere(['<', 'dispatch_time', $time + 86400]);
        }
        if($search_ratio ==''){
            $query->andWhere(new Expression('card.data > card.total_flow'));
        }
        if($search_ratio == 1){
            $query->andWhere(new Expression('card.data / card.total_flow >=0.5'));
        }
        if($search_ratio == 2){
            $query->andWhere(new Expression('card.data / card.total_flow >=0.8'));
        }
        if($search_ratio == 3){
            $query->andWhere(new Expression('card.data / card.total_flow >=1'));
        }
        
        $searchValidity = $this->get('search_validity',0) == 1;
        if($searchValidity){
            $query->andFilterWhere(['>=','term_end_time',0]);
            $query->andFilterWhere(['>','term_end_time',time()]);
        }
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $sortName = $this->get('sort_name', 'card.refresh_time');
        $sortOrder = $this->get('sort_order', 'DESC');
        if (!empty($sortName) && !in_array($sortName, ['card.id', 'card.iccid', 'card.msisdn', 'card.dispatch_time', 'card.refresh_time', 'card.data', 'card.term_end_time'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        if (!empty($sortOrder) && !in_array($sortOrder, ['ASC', 'DESC'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        $query->orderBy([
            $sortName => ['ASC' => SORT_ASC, 'DESC' => SORT_DESC][$sortOrder],
        ]);
        if ($this->get('export') == 'excel') { // 导出Excel文件
            try {
                $excel = new Spreadsheet();
                $sheet = $excel->setActiveSheetIndex(0);
                foreach ([
                             '编号',
                             '代理商编号',
                             '代理商名',
                             '用户编号',
                             '用户名',
                             '套餐id',
                             '套餐',
                             'ICCID',
                             'MSISDN',
                             'IMSI',
                             '状态',
                             '分配时间',
                             '激活时间',
                             '开关机',
                             '数据用量（本周期）',
                             '总流量',
                             '运营商流量',
                             '套餐有效期',
                             '套餐结束时间',
                         ] as $index => $title) {
                    $sheet->setCellValue(chr(65 + $index) . '1', $title);
                }
                $sheet->getStyle('A1:Z1')->applyFromArray(['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]]);
                $r = 2;
                /** @var Card $card */
                foreach ($query->each() as $card) {
                    $sheet->setCellValueExplicitByColumnAndRow(1, $r, $card->id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(2, $r, $card->agent_id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(3, $r, empty($card->agent_id) ? '' : $card->agent->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, $r, $card->uid, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(5, $r, empty($card->uid) ? '' : $card->user->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(6, $r, $card->package_id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(7, $r, empty($card->package_id) ? '' : $card->package->name, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(8, $r, $card->iccid, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(9, $r, $card->msisdn, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(10, $r, $card->imsi, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(11, $r, KeyMap::getValue('card_status', $card->status), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(12, $r, Yii::$app->formatter->asDatetime($card->dispatch_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(13, $r, Yii::$app->formatter->asDatetime($card->active_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(14, $r, $card->on_off, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(15, $r, Yii::$app->formatter->asShortSize($card->data), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(16, $r, Yii::$app->formatter->asShortSize($card->total_flow), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(17, $r, Yii::$app->formatter->asShortSize($card->data_isp), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(18, $r, $card->term_length . KeyMap::getValue('card_term_length_unit', $card->term_length_unit), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(19, $r, Yii::$app->formatter->asDatetime($card->term_end_time), DataType::TYPE_STRING);
                    $r++;
                }
                $sheet->freezePane('A2');
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.xls"');
                header('Cache-Control: max-age=0');
                $excelWriter = IOFactory::createWriter($excel, 'Xls');
                $excelWriter->save('php://output');
            } catch (Exception $e) {
                Yii::error('导出Excel文件时出错：' . $e->getMessage());
                Yii::error($e->getTraceAsString());
                echo $e->getMessage();
            }
            die();
        }
        if ($this->get('export') == 'csv') { // 导出CSV文件
            header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
            header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.csv"');
            header('Cache-Control: max-age=0');
            echo "编号,代理商编号,用户编号,套餐,ICCID,MSISDN,IMSI,状态,分配时间,激活时间,开关机,数据用量（本月）（B）,套餐有效期,套餐结束时间\r\n";
            /** @var Card $card */
            foreach ($query->each() as $card) {
                echo $card->id, ',';
                echo $card->agent_id, ',';
                echo $card->uid, ',';
                echo empty($card->package_id) ? '' : $card->package->name, ',';
                echo $card->iccid, ',';
                echo $card->msisdn, ',';
                echo $card->imsi, ',';
                echo KeyMap::getValue('card_status', $card->status), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->dispatch_time), ',';
                } catch (InvalidConfigException $e) {
                    echo ',';
                }
                echo $card->on_off, ',';
                echo $card->data, ',';
                echo $card->term_length, KeyMap::getValue('card_term_length_unit', $card->term_length_unit), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->term_end_time);
                } catch (InvalidConfigException $e) {
                }
                echo "\r\n";
            }
            die();
        }
        $query->offset($pagination->offset)->limit($pagination->limit);
        $cardList = $query->all();
        return $this->render('card_list_overload', [
            'cardList' => $cardList,
            'pagination' => $pagination,
        ]);
    }
    //卡充值历史
    public function actionCardHistory(){
        if (!$this->manager->can('card/card-history')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        /*$arr = Yii::$app->db->createCommand('SELECT `card_history`.`card_id` FROM card_history WHERE `card_history`.`DATA` > `card_history`.`total_flow` GROUP BY `card_history`.`card_id` HAVING COUNT(1)>1')
           ->queryAll();*/
         $arr = Yii::$app->db->createCommand('SELECT `card_history`.`card_id` FROM card_history WHERE `card_history`.`DATA` > `card_history`.`total_flow`')
           ->queryAll();  
        $card_id = array_column($arr,'card_id');
        $im = implode(',',$card_id);
        $ids = explode(',', $im);
        $query = Card::find()->alias('card');
        if (!empty($this->get('search_agent'))) {
            /** @var Agent $searchAgent */
            $searchAgent = Agent::find()
                ->andWhere(['username' => $this->get('search_agent')])
                ->one();
            if (empty($searchAgent)) {
                throw new NotFoundHttpException('没有找到代理商【' . $this->get('search_agent') . '】。');
            } else {
                $agentIdList = [$searchAgent->id];
                if ($this->get('search_sub_agent') == 1) { // 包含下级代理商
                    $agentIdList = array_merge($agentIdList, $searchAgent->getSubAidList());
                }
                $query->andWhere(['agent_id' => $agentIdList]);
            }
        }
        if (!empty($this->get('search_username'))) {
            $query->joinWith('user user')->andWhere(['user.username' => $this->get('search_username')]);
        }
        $query->andFilterWhere(['status' => 4]);
        $query->andFilterWhere(['card.status' => $this->get('search_status')]);
        $query->andFilterWhere(['like', 'card.iccid', $this->get('search_iccid').'%', false]);
        $query->andFilterWhere(['card.msisdn' => $this->get('search_msisdn')]);
        $query->andFilterWhere(['card.batch_no' => $this->get('search_batch_no')]);
       // $query->andWhere(['in','card.id',$ids]); 
        $sortName = $this->get('sort_name', 'card.refresh_time');
        $sortOrder = $this->get('sort_order', 'DESC');
        if (!empty($sortName) && !in_array($sortName, ['card.id', 'card.iccid', 'card.msisdn', 'card.dispatch_time', 'card.refresh_time', 'card.data', 'card.term_end_time'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        if (!empty($sortOrder) && !in_array($sortOrder, ['ASC', 'DESC'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        $query->orderBy([
            $sortName => ['ASC' => SORT_ASC, 'DESC' => SORT_DESC][$sortOrder],
        ]);
        $pagination = new Pagination(['totalCount' => $query->count()]);
        if ($this->get('export') == 'excel') { // 导出Excel文件
            try {
                $excel = new Spreadsheet();
                $sheet = $excel->setActiveSheetIndex(0);
                foreach ([
                             '编号',
                             '代理商编号',
                             '代理商名',
                             '用户编号',
                             '用户名',
                             '套餐id',
                             '套餐',
                             'ICCID',
                             'MSISDN',
                             'IMSI',
                             '状态',
                             '分配时间',
                             '激活时间',
                             '开关机',
                             '数据用量（本周期）',
                             '总流量',
                             '运营商流量',
                             '套餐有效期',
                             '套餐结束时间',
                         ] as $index => $title) {
                    $sheet->setCellValue(chr(65 + $index) . '1', $title);
                }
                $sheet->getStyle('A1:Z1')->applyFromArray(['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]]);
                $r = 2;
                /** @var Card $card */
                foreach ($query->each() as $card) {
                    $sheet->setCellValueExplicitByColumnAndRow(1, $r, $card->id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(2, $r, $card->agent_id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(3, $r, empty($card->agent_id) ? '' : $card->agent->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, $r, $card->uid, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(5, $r, empty($card->uid) ? '' : $card->user->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(6, $r, $card->package_id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(7, $r, empty($card->package_id) ? '' : $card->package->name, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(8, $r, $card->iccid, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(9, $r, $card->msisdn, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(10, $r, $card->imsi, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(11, $r, KeyMap::getValue('card_status', $card->status), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(12, $r, Yii::$app->formatter->asDatetime($card->dispatch_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(13, $r, Yii::$app->formatter->asDatetime($card->active_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(14, $r, $card->on_off, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(15, $r, Yii::$app->formatter->asShortSize($card->data), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(16, $r, Yii::$app->formatter->asShortSize($card->total_flow), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(17, $r, Yii::$app->formatter->asShortSize($card->data_isp), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(18, $r, $card->term_length . KeyMap::getValue('card_term_length_unit', $card->term_length_unit), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(19, $r, Yii::$app->formatter->asDatetime($card->term_end_time), DataType::TYPE_STRING);
                    $r++;
                }
                $sheet->freezePane('A2');
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.xls"');
                header('Cache-Control: max-age=0');
                $excelWriter = IOFactory::createWriter($excel, 'Xls');
                $excelWriter->save('php://output');
            } catch (Exception $e) {
                Yii::error('导出Excel文件时出错：' . $e->getMessage());
                Yii::error($e->getTraceAsString());
                echo $e->getMessage();
            }
            die();
        }
        if ($this->get('export') == 'csv') { // 导出CSV文件
            header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
            header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.csv"');
            header('Cache-Control: max-age=0');
            echo "编号,代理商编号,用户编号,套餐,ICCID,MSISDN,IMSI,状态,分配时间,激活时间,开关机,数据用量（本月）（B）,套餐有效期,套餐结束时间\r\n";
            /** @var Card $card */
            foreach ($query->each() as $card) {
                echo $card->id, ',';
                echo $card->agent_id, ',';
                echo $card->uid, ',';
                echo empty($card->package_id) ? '' : $card->package->name, ',';
                echo $card->iccid, ',';
                echo $card->msisdn, ',';
                echo $card->imsi, ',';
                echo KeyMap::getValue('card_status', $card->status), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->dispatch_time), ',';
                } catch (InvalidConfigException $e) {
                    echo ',';
                }
                echo $card->on_off, ',';
                echo $card->data, ',';
                echo $card->term_length, KeyMap::getValue('card_term_length_unit', $card->term_length_unit), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->term_end_time);
                } catch (InvalidConfigException $e) {
                }
                echo "\r\n";
            }
            die();
        }
        $query->offset($pagination->offset)->limit($pagination->limit);
        $cardList = $query->all();
        return $this->render('card_history',[
            'cardList' => $cardList,
            'pagination' => $pagination,
        ]);
    }
    /**
     * 卡历史记录
     */
    public function actionHistoryList(){
        if (!$this->manager->can('card/history-list')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('card_id');
        $query = CardHistory::find();
        $query->where(['card_id'=>$id]);
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $query->offset($pagination->offset)->limit($pagination->limit);
        $cardList = $query->all();
        return $this->render('history_list',[
            'cardList' => $cardList,
            'pagination' => $pagination,
        ]);

    }
    /**
     * 到期提醒卡列表
     * @return string
     * @throws BadRequestHttpException
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionListEndDayAlert()
    {
        return $this->actionList();
    }

    /**
     * 已到期未停用卡列表
     * @return string
     * @throws ForbiddenHttpException
     * @throws BadRequestHttpException
     * @throws NotFoundHttpException
     */
    public function actionOverLoadList()
    {
        if (!$this->manager->can('card/over-load-list')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $query = Card::find()->alias('card');
        $query->joinWith('isp isp');
        $query->andWhere(['isp.status' => Isp::STATUS_OK]);
        if (!empty($this->get('search_agent'))) {
            /** @var Agent $searchAgent */
            $searchAgent = Agent::find()
                ->andWhere(['username' => $this->get('search_agent')])
                ->one();
            if (empty($searchAgent)) {
                throw new NotFoundHttpException('没有找到代理商【' . $this->get('search_agent') . '】。');
            } else {
                $agentIdList = [$searchAgent->id];
                if ($this->get('search_sub_agent') == 1) { // 包含下级代理商
                    $agentIdList = array_merge($agentIdList, $searchAgent->getSubAidList());
                }
                $query->andWhere(['agent_id' => $agentIdList]);
            }
        }
        if (!empty($this->get('search_username'))) {
            $query->joinWith('user user')->andWhere(['user.username' => $this->get('search_username')]);
        }
        $query->andFilterWhere(['card.status' => $this->get('search_status')]);
        //$query->andFilterWhere(['card.iccid' => $this->get('search_iccid')]);
        $query->andFilterWhere(['like', 'card.iccid', $this->get('search_iccid').'%', false]);
        $query->andFilterWhere(['card.msisdn' => $this->get('search_msisdn')]);
        $query->andFilterWhere(['card.batch_no' => $this->get('search_batch_no')]);
        $query->andFilterWhere(['card.api_id' => $this->get('search_api_id')]);
        $query->andFilterWhere(['card.isp_id' => $this->get('search_isp_id')]);
        $query->andFilterWhere(['card.package_id' => $this->get('search_package_id')]);
        $query->andFilterWhere(['card.status'=> 4]);
        $query->andFilterWhere(['>=','active_time',0]);
        $query->andFilterWhere(['<','term_end_time',time()]);
        if (!empty($this->get('search_dispatch_time'))) {
            $time = strtotime($this->get('search_dispatch_time'));
            $query->andWhere(['>=', 'dispatch_time', $time]);
            $query->andWhere(['<', 'dispatch_time', $time + 86400]);
        }
        $searchEndDayAlert = $this->get('search_end_day_alert');
        if (!empty($searchEndDayAlert)) { // 到期提醒的卡
            $query->andWhere(['>', 'card.term_end_time', 0]);
            $query->andWhere(['<=', 'card.term_end_time', time() + $searchEndDayAlert * 86400]);
        }
        $searchOverload = $this->get('search_overload', 0) == 1;
        if ($searchOverload) {
            $query->joinWith('package package');
            $query->andWhere(new Expression('card.data > package.data'));
        }
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $sortName = $this->get('sort_name', 'card.refresh_time');
        $sortOrder = $this->get('sort_order', 'DESC');
        if (!empty($sortName) && !in_array($sortName, ['card.id', 'card.iccid', 'card.msisdn', 'card.dispatch_time', 'card.refresh_time', 'card.data', 'card.term_end_time'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        if (!empty($sortOrder) && !in_array($sortOrder, ['ASC', 'DESC'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        $query->orderBy([
            $sortName => ['ASC' => SORT_ASC, 'DESC' => SORT_DESC][$sortOrder],
        ]);
        if ($this->get('export') == 'excel') { // 导出Excel文件
            try {
                $excel = new Spreadsheet();
                $sheet = $excel->setActiveSheetIndex(0);
                foreach ([
                             '编号',
                             '代理商编号',
                             '代理商名',
                             '用户编号',
                             '用户名',
                             '套餐id',
                             '套餐',
                             'ICCID',
                             'MSISDN',
                             'IMSI',
                             '状态',
                             '分配时间',
                             '激活时间',
                             '开关机',
                             '数据用量（本周期）',
                             '总流量',
                             '运营商流量',
                             '套餐有效期',
                             '套餐结束时间',
                         ] as $index => $title) {
                    $sheet->setCellValue(chr(65 + $index) . '1', $title);
                }
                $sheet->getStyle('A1:Z1')->applyFromArray(['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]]);
                $r = 2;
                /** @var Card $card */
                foreach ($query->each() as $card) {
                    $sheet->setCellValueExplicitByColumnAndRow(1, $r, $card->id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(2, $r, $card->agent_id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(3, $r, empty($card->agent_id) ? '' : $card->agent->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, $r, $card->uid, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(5, $r, empty($card->uid) ? '' : $card->user->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(6, $r, $card->package_id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(7, $r, empty($card->package_id) ? '' : $card->package->name, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(8, $r, $card->iccid, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(9, $r, $card->msisdn, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(10, $r, $card->imsi, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(11, $r, KeyMap::getValue('card_status', $card->status), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(12, $r, Yii::$app->formatter->asDatetime($card->dispatch_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(13, $r, Yii::$app->formatter->asDatetime($card->active_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(14, $r, $card->on_off, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(15, $r, Yii::$app->formatter->asShortSize($card->data), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(16, $r, Yii::$app->formatter->asShortSize($card->total_flow), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(17, $r, Yii::$app->formatter->asShortSize($card->data_isp), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(18, $r, $card->term_length . KeyMap::getValue('card_term_length_unit', $card->term_length_unit), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(19, $r, Yii::$app->formatter->asDatetime($card->term_end_time), DataType::TYPE_STRING);
                    $r++;
                }
                $sheet->freezePane('A2');
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.xls"');
                header('Cache-Control: max-age=0');
                $excelWriter = IOFactory::createWriter($excel, 'Xls');
                $excelWriter->save('php://output');
            } catch (Exception $e) {
                Yii::error('导出Excel文件时出错：' . $e->getMessage());
                Yii::error($e->getTraceAsString());
                echo $e->getMessage();
            }
            die();
        }
        if ($this->get('export') == 'csv') { // 导出CSV文件
            header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
            header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.csv"');
            header('Cache-Control: max-age=0');
            echo "编号,代理商编号,用户编号,套餐,ICCID,MSISDN,IMSI,状态,分配时间,激活时间,开关机,数据用量（本月）（B）,套餐有效期,套餐结束时间\r\n";
            /** @var Card $card */
            foreach ($query->each() as $card) {
                echo $card->id, ',';
                echo $card->agent_id, ',';
                echo $card->uid, ',';
                echo empty($card->package_id) ? '' : $card->package->name, ',';
                echo $card->iccid, ',';
                echo $card->msisdn, ',';
                echo $card->imsi, ',';
                echo KeyMap::getValue('card_status', $card->status), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->dispatch_time), ',';
                } catch (InvalidConfigException $e) {
                    echo ',';
                }
                echo $card->on_off, ',';
                echo $card->data, ',';
                echo $card->term_length, KeyMap::getValue('card_term_length_unit', $card->term_length_unit), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->term_end_time);
                } catch (InvalidConfigException $e) {
                }
                echo "\r\n";
            }
            die();
        }
        $query->offset($pagination->offset)->limit($pagination->limit);
        $cardList = $query->all();
        return $this->render('card_over_load', [
            'cardList' => $cardList,
            'pagination' => $pagination,
        ]);
    }
    /**
     * 流量超出未停用卡列表
     * @return string
     * @throws ForbiddenHttpException
     * @throws BadRequestHttpException
     * @throws NotFoundHttpException
     */
    public function actionFlowOverList()
    {
        if (!$this->manager->can('card/flow-over-list')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $query = Card::find()->alias('card');
        $query->joinWith('isp isp');
        $query->andWhere(['isp.status' => Isp::STATUS_OK]);
        if (!empty($this->get('search_agent'))) {
            /** @var Agent $searchAgent */
            $searchAgent = Agent::find()
                ->andWhere(['username' => $this->get('search_agent')])
                ->one();
            if (empty($searchAgent)) {
                throw new NotFoundHttpException('没有找到代理商【' . $this->get('search_agent') . '】。');
            } else {
                $agentIdList = [$searchAgent->id];
                if ($this->get('search_sub_agent') == 1) { // 包含下级代理商
                    $agentIdList = array_merge($agentIdList, $searchAgent->getSubAidList());
                }
                $query->andWhere(['agent_id' => $agentIdList]);
            }
        }
        if (!empty($this->get('search_username'))) {
            $query->joinWith('user user')->andWhere(['user.username' => $this->get('search_username')]);
        }
        $query->andFilterWhere(['card.status' => $this->get('search_status')]);
        //$query->andFilterWhere(['card.iccid' => $this->get('search_iccid')]);
        $query->andFilterWhere(['like', 'card.iccid', $this->get('search_iccid').'%', false]);
        $query->andFilterWhere(['card.msisdn' => $this->get('search_msisdn')]);
        $query->andFilterWhere(['card.batch_no' => $this->get('search_batch_no')]);
        $query->andFilterWhere(['card.api_id' => $this->get('search_api_id')]);
        $query->andFilterWhere(['card.isp_id' => $this->get('search_isp_id')]);
        $query->andFilterWhere(['card.package_id' => $this->get('search_package_id')]);
        $query->andFilterWhere(['card.status'=> 4]);
        $query->andFilterWhere(['>','card.data',0]);
        $query->andFilterWhere(['>','card.total_flow',0]);
        $query->andWhere(new Expression('card.data >= card.total_flow'));
        if (!empty($this->get('search_dispatch_time'))) {
            $time = strtotime($this->get('search_dispatch_time'));
            $query->andWhere(['>=', 'dispatch_time', $time]);
            $query->andWhere(['<', 'dispatch_time', $time + 86400]);
        }
        $searchEndDayAlert = $this->get('search_end_day_alert');
        if (!empty($searchEndDayAlert)) { // 到期提醒的卡
            $query->andWhere(['>', 'card.term_end_time', 0]);
            $query->andWhere(['<=', 'card.term_end_time', time() + $searchEndDayAlert * 86400]);
        }
        $searchOverload = $this->get('search_overload', 0) == 1;
        if ($searchOverload) {
            $query->joinWith('package package');
            $query->andWhere(new Expression('card.data > package.data'));
        }
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $sortName = $this->get('sort_name', 'card.refresh_time');
        $sortOrder = $this->get('sort_order', 'DESC');
        if (!empty($sortName) && !in_array($sortName, ['card.id', 'card.iccid', 'card.msisdn', 'card.dispatch_time', 'card.refresh_time', 'card.data', 'card.term_end_time'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        if (!empty($sortOrder) && !in_array($sortOrder, ['ASC', 'DESC'])) {
            throw new BadRequestHttpException('参数错误。');
        }
        $query->orderBy([
            $sortName => ['ASC' => SORT_ASC, 'DESC' => SORT_DESC][$sortOrder],
        ]);
        if ($this->get('export') == 'excel') { // 导出Excel文件
            try {
                $excel = new Spreadsheet();
                $sheet = $excel->setActiveSheetIndex(0);
                foreach ([
                             '编号',
                             '代理商编号',
                             '代理商名',
                             '用户编号',
                             '用户名',
                             '套餐id',
                             '套餐',
                             'ICCID',
                             'MSISDN',
                             'IMSI',
                             '状态',
                             '分配时间',
                             '激活时间',
                             '开关机',
                             '数据用量（本周期）',
                             '总流量',
                             '运营商流量',
                             '套餐有效期',
                             '套餐结束时间',
                         ] as $index => $title) {
                    $sheet->setCellValue(chr(65 + $index) . '1', $title);
                }
                $sheet->getStyle('A1:Z1')->applyFromArray(['font' => ['bold' => true], 'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER]]);
                $r = 2;
                /** @var Card $card */
                foreach ($query->each() as $card) {
                    $sheet->setCellValueExplicitByColumnAndRow(1, $r, $card->id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(2, $r, $card->agent_id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(3, $r, empty($card->agent_id) ? '' : $card->agent->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, $r, $card->uid, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(5, $r, empty($card->uid) ? '' : $card->user->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(6, $r, $card->package_id, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(7, $r, empty($card->package_id) ? '' : $card->package->name, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(8, $r, $card->iccid, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(9, $r, $card->msisdn, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(10, $r, $card->imsi, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(11, $r, KeyMap::getValue('card_status', $card->status), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(12, $r, Yii::$app->formatter->asDatetime($card->dispatch_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(13, $r, Yii::$app->formatter->asDatetime($card->active_time), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(14, $r, $card->on_off, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(15, $r, Yii::$app->formatter->asShortSize($card->data), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(16, $r, Yii::$app->formatter->asShortSize($card->total_flow), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(17, $r, Yii::$app->formatter->asShortSize($card->data_isp), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(18, $r, $card->term_length . KeyMap::getValue('card_term_length_unit', $card->term_length_unit), DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(19, $r, Yii::$app->formatter->asDatetime($card->term_end_time), DataType::TYPE_STRING);
                    $r++;
                }
                $sheet->freezePane('A2');
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.xls"');
                header('Cache-Control: max-age=0');
                $excelWriter = IOFactory::createWriter($excel, 'Xls');
                $excelWriter->save('php://output');
            } catch (Exception $e) {
                Yii::error('导出Excel文件时出错：' . $e->getMessage());
                Yii::error($e->getTraceAsString());
                echo $e->getMessage();
            }
            die();
        }
        if ($this->get('export') == 'csv') { // 导出CSV文件
            header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
            header('Content-Disposition: attachment;filename="card_' . date('Y-m-d') . '.csv"');
            header('Cache-Control: max-age=0');
            echo "编号,代理商编号,用户编号,套餐,ICCID,MSISDN,IMSI,状态,分配时间,激活时间,开关机,数据用量（本月）（B）,套餐有效期,套餐结束时间\r\n";
            /** @var Card $card */
            foreach ($query->each() as $card) {
                echo $card->id, ',';
                echo $card->agent_id, ',';
                echo $card->uid, ',';
                echo empty($card->package_id) ? '' : $card->package->name, ',';
                echo $card->iccid, ',';
                echo $card->msisdn, ',';
                echo $card->imsi, ',';
                echo KeyMap::getValue('card_status', $card->status), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->dispatch_time), ',';
                } catch (InvalidConfigException $e) {
                    echo ',';
                }
                echo $card->on_off, ',';
                echo $card->data, ',';
                echo $card->term_length, KeyMap::getValue('card_term_length_unit', $card->term_length_unit), ',';
                try {
                    echo Yii::$app->formatter->asDatetime($card->term_end_time);
                } catch (InvalidConfigException $e) {
                }
                echo "\r\n";
            }
            die();
        }
        $query->offset($pagination->offset)->limit($pagination->limit);
        $cardList = $query->all();
        return $this->render('flow_over_list', [
            'cardList' => $cardList,
            'pagination' => $pagination,
        ]);
    }
    /**
     * 超流量卡列表
     * @return string
     * @throws BadRequestHttpException
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionListOverload()
    {
        return $this->actionList();
    }

    /**
     * 批量设置套餐
     * @return string
     * @throws ForbiddenHttpException
     */
    public function actionSetPackage()
    {
        if (!$this->manager->can('card/edit')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $model = new CardSetPackageForm();
        $model->setAttributes($this->get());
        $model->source = 'iccid';
        if ($model->load($this->post()) && $model->setPackage()) {
            ManagerLog::info($this->manager->id, '批量设置套餐', print_r($model->attributes, true));
            Yii::$app->session->addFlash('success', '分配完成。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/set-package']),
                'txt' => '卡列表'
            ]));
        }
        return $this->render('card_set_package', [
            'model' => $model,
        ]);
    }

    /**
     * 分配用户
     * @return string
     * @throws ForbiddenHttpException
     */
    public function actionDispatch()
    {
        if (!$this->manager->can('card/dispatch')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $model = new CardDispatchForm();
        $model->setAttributes($this->get());
        $model->source = 'iccid';
        $model->user_type = 'user';
        if ($model->load($this->post()) && $model->dispatch()) {
            ManagerLog::info($this->manager->id, '分配卡', print_r($model->attributes, true));
            Yii::$app->session->addFlash('success', '分配完成。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/dispatch']),
                'txt' => '卡列表'
            ]));
        }
        return $this->render('card_dispatch', [
            'model' => $model,
        ]);
    }

    /**
     * 批量设置套餐有效期
     * @return string
     * @throws ForbiddenHttpException
     */
    public function actionSetTerm()
    {
        if (!$this->manager->can('card/edit')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $model = new CardSetTermForm();
        $model->setAttributes($this->get());
        $model->source = 'iccid';
        if ($model->load($this->post()) && $model->setTerm()) {
            ManagerLog::info($this->manager->id, '批量设置套餐有效期', print_r($model->attributes, true));
            Yii::$app->session->addFlash('success', '设置完成。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/set-term']),
                'txt' => '卡列表'
            ]));
        }
        return $this->render('card_set_term', [
            'model' => $model,
        ]);
    }

    /**
     * 刷新卡信息AJAX接口
     * @return array
     */
    public function actionRefresh()
    {
        if (!$this->manager->can('card/list')) {
            return ['error' => 'SERVER', 'message' => '没有权限。'];
        }
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id]);
        if (empty($card)) {
            return ['error' => 'SERVER', 'message' => '没有找到卡信息。'];
        }
        try {
            $card->refreshBasic();
            return [];
        } catch (Exception $e) {
            return ['error' => 'SERVER', 'message' => $e->getMessage()];
        }
    }
    /**
     * 新刷新卡信息
     * @return array
     */
    public function actionNewRefresh(){
        if(!$this->manager->can('card/list')){
            return ['error'=> 'SERVER','message' => '没有权限'];
        }
        $id = $this->get('id');
        $card = Card::findOne(['id'=>$id]);
        if(empty($card)){
            return ['error' => 'SERVER','message'=>'没有找到卡信息'];
        }
        try {
            $card->newRefreshBasic();
            return [];
        } catch(Exception $e){
            return ['error'=>'SERVER','message'=>$e->getMessage()];
        }
    }
    /**
     * 强制设备断开网络，使其重新注册AJAX接口
     * @return array
     */
    public function actionRestart()
    {
        if (!$this->manager->can('card/list')) {
            return ['error' => 'SERVER', 'message' => '没有权限。'];
        }
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id]);
        if (empty($card)) {
            return ['error' => 'SERVER', 'message' => '没有找到卡信息。'];
        }
        if ($card->api_id == 1) {
            $api = new \app\models\jasper\Api($card->isp->data);
            try {
                $resp = $api->SendCancelLocation($card->iccid);
                return ['resp' => $resp];
            } catch (Exception $e) {
                return ['error' => 'SERVER', 'message' => $e->getMessage()];
            }
        } else {
            return ['error' => 'SERVER', 'message' => '不支持此操作。'];
        }
    }

    /**
     * 设置卡状态AJAX接口
     * @return array
     */
    public function actionSetCardStatus()
    {
        if (!$this->manager->can('card/list')) {
            return ['error' => 'SERVER', 'message' => '没有权限。'];
        }
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id]);
        if (empty($card)) {
            return ['error' => 'SERVER', 'message' => '没有找到卡信息。'];
        }
        $status = $this->get('status');
        if (!in_array($status, Card::STATUS_TRANSFORM_MAP[$card->status])) {
            return ['error' => 'SERVER', 'message' => '无法将卡设置为：' . KeyMap::getValue('card_status', $status)];
        }
        try {
            //$card->setStatus($status);
            $card->setExecute($card->iccid,$status);
            CardStatusChange::info($id, $card->status, $status, 1, 'manage_id=' . $this->manager->id);
            return [];
        } catch (Exception $e) {
            return ['error' => 'SERVER', 'message' => $e->getMessage()];
        }
    }

    /**
     * 卡详情
     * @return string
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionView()
    {
        if (!$this->manager->can('card/list')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id]);
        if (empty($card)) {
            throw new NotFoundHttpException('没有找到卡信息。');
        }
        $this->layout = false; //不使用默认布局
        $this->layout = "new";
        return $this->render('card_view', [
            'card' => $card,
        ]);
    }

    /**
     * 添加修改卡
     * @return string
     * @throws ForbiddenHttpException
     * @throws NotFoundHttpException
     */
    public function actionEdit()
    {
        if (!$this->manager->can('card/edit')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        if (!empty($id)) {
            $card = Card::findOne(['id' => $id]);
            if (empty($card)) {
                throw new NotFoundHttpException('没有找到卡信息。');
            }
        } else {
            $card = new Card();
            $card->status = Card::STATUS_TEST_READY;
            $card->batch_no = Card::generateBatchNo($this->manager->id);
            $card->create_time = time();
        }
        if ($card->load($this->post()) && $card->save()) {
            ManagerLog::info($this->manager->id, '保存卡', print_r($card->attributes, true));
            Yii::$app->session->addFlash('success', '数据已保存。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/edit']),
                'txt' => '卡列表'
            ]));
        }
        return $this->render('card_edit', [
            'card' => $card,
        ]);
    }

    /**
     * 导入卡
     * @throws ForbiddenHttpException
     * @return string
     */
    public function actionImport()
    {
        if (!$this->manager->can('card/edit')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $model = new CardImportForm();
        if ($model->load($this->post()) && $model->import($this->manager->id)) {
            ManagerLog::info($this->manager->id, '导入卡', print_r($model->attributes, true));
            Yii::$app->session->addFlash('success', '数据已保存。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/import']),
                'txt' => '卡列表'
            ]));
        }
        return $this->render('card_import', [
            'model' => $model,
        ]);
    }

    /**
     * 回收用户卡AJAX接口
     * @return array
     */
    public function actionRevokeFromUser()
    {
        if (!$this->manager->can('card/revoke')) {
            return ['error' => 'SERVER', 'message' => '没有权限。'];
        }
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id]);
        if (empty($card)) {
            return ['error' => 'SERVER', 'message' => '没有找到卡信息。'];
        }
        if (empty($card->uid)) {
            return ['error' => 'SERVER', 'message' => '此卡没有分配用户。'];
        }
        $card->uid = null;
        $card->save(false);
        return [];
    }

    /**
     * 回收代理商卡AJAX接口
     * @return array
     */
    public function actionRevokeFromAgent()
    {
        if (!$this->manager->can('card/revoke')) {
            return ['error' => 'SERVER', 'message' => '没有权限。'];
        }
        $id = $this->get('id');
        $card = Card::findOne(['id' => $id]);
        if (empty($card)) {
            return ['error' => 'SERVER', 'message' => '没有找到卡信息。'];
        }
        if (empty($card->agent_id)) {
            return ['error' => 'SERVER', 'message' => '此卡没有分配给代理商。'];
        }
        // 回收代理商卡相当于将卡分配给上级代理（如果上级代理没有，则直接设置为null）
        $card->agent_id = $card->agent->pid;
        $card->save(false);
        return [];
    }

    /**
     * 账单列表
     * @return string
     * @throws ForbiddenHttpException
     */
    public function actionBill()
    {
        if (!$this->manager->can('card/bill')) {
            throw new ForbiddenHttpException('没有权限。');
        }

        $query = CardBill::find()->alias('bill');
        $searchMonth = $this->get('search_month');
        if (!empty($searchMonth)) {
            $query->andWhere(['bill.month' => preg_replace('/\D/', '', $searchMonth)]);
        }
        if (!empty($this->get('search_username'))) {
            $query->joinWith(['card card', 'card.user user']);
            $query->andWhere(['user.username' => $this->get('search_username')]);
            /** @var User $user */
            $user = User::findOne(['username' => $this->get('search_username')]);
        }
        $query->andFilterWhere(['card.package_id' => $this->get('search_package_id')]);
        $searchIccid = $this->get('search_iccid');
        if (!empty($searchIccid)) {
            $query->joinWith('card card')->andWhere(['card.iccid' => $searchIccid]);
        }
        if ($this->get('export') == 'excel') { // 导出Excel文件
            try {
                $excel = new Spreadsheet();
                /** @var CardAttr[] $attrList 卡自定义属性列表 */
                $attrList = empty($user) ? [] : CardAttr::find()->andWhere(['uid' => $user->id])->all();
                /** @var Package[] $packageList 套餐列表 */
                $packageList = [];
                /** @var array $cardAmount 不同套餐计费数量 */
                $cardAmount = [];
                $query->orderBy('id DESC');
                /** @var CardBill $bill */
                foreach ($query->each() as $bill) {
                    if (!isset($packageList[$bill->package_id])) {
                        $packageList[$bill->package_id] = $bill->package;
                        $cardAmount[$bill->package_id] = 0;
                    }
                    $cardAmount[$bill->package_id] += 1;
                }
                $month = $this->get('search_month');
                $sheet = $excel->setActiveSheetIndex(0);
                $sheet->setCellValueExplicitByColumnAndRow(1, 1, '用户名称', DataType::TYPE_STRING);
                $sheet->setCellValueExplicitByColumnAndRow(2, 1, '套餐', DataType::TYPE_STRING);
                $sheet->setCellValueExplicitByColumnAndRow(3, 1, '月份', DataType::TYPE_STRING);
                $sheet->setCellValueExplicitByColumnAndRow(4, 1, '计费数量（张）', DataType::TYPE_STRING);
                $sheet->setCellValueExplicitByColumnAndRow(5, 1, '单价（元）', DataType::TYPE_STRING);
                $sheet->setCellValueExplicitByColumnAndRow(6, 1, '金额（元）', DataType::TYPE_STRING);
                $i = 0;
                foreach ($packageList as $package) {
                    $sheet->setCellValueExplicitByColumnAndRow(1, 2 + $i, $bill->user->username, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(2, 2 + $i, $package->name, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(3, 2 + $i, $month, DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, 2 + $i, $cardAmount[$package->id], DataType::TYPE_NUMERIC);
                    $userPackage = UserPackage::findOne(['uid' => $bill->user->id, 'package_id' => $package->id]);
                    $sheet->setCellValueExplicitByColumnAndRow(5, 2 + $i, $userPackage->cost_price, DataType::TYPE_NUMERIC);
                    $sheet->setCellValueExplicitByColumnAndRow(6, 2 + $i, $userPackage->cost_price * $cardAmount[$package->id], DataType::TYPE_NUMERIC);
                    $i += 1;
                }
                if (count($packageList) > 1) {
                    $sheet->mergeCellsByColumnAndRow(1, 2, 1, 1 + count($packageList));
                }
                $sheet->getColumnDimension('A')->setWidth(15);
                $sheet->getColumnDimension('B')->setWidth(20);
                $sheet->getColumnDimension('C')->setWidth(10);
                $sheet->getColumnDimension('D')->setWidth(10);
                $sheet->getColumnDimension('E')->setWidth(10);
                $sheet->getColumnDimension('F')->setWidth(10);
                $sheet->getDefaultRowDimension()->setRowHeight(30);
                $sheet->freezePane('A2');
                $sheet->setTitle('总账单');
                foreach ($packageList as $package) {
                    $userPackage = UserPackage::findOne(['uid' => $bill->user->id, 'package_id' => $package->id]);
                    $sheet = $excel->createSheet();
                    $sheet->freezePane('A2');
                    $sheet->setTitle($package->name);
                    $sheet->setCellValueExplicitByColumnAndRow(1, 1, 'ICCID', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(2, 1, '客户', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(3, 1, '套餐', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(4, 1, '费用', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(5, 1, '流量（B）', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(6, 1, '短信（条）', DataType::TYPE_STRING);
                    $sheet->setCellValueExplicitByColumnAndRow(7, 1, '语音（分钟）', DataType::TYPE_STRING);
                    $sheet->getColumnDimension('A')->setWidth(25);
                    $sheet->getColumnDimension('B')->setWidth(15);
                    $sheet->getColumnDimension('C')->setWidth(20);
                    $sheet->getColumnDimension('D')->setWidth(10);
                    $sheet->getColumnDimension('E')->setWidth(10);
                    $sheet->getColumnDimension('F')->setWidth(10);
                    $sheet->getColumnDimension('G')->setWidth(10);
                    $c = 8;
                    foreach ($attrList as $attr) {
                        $sheet->setCellValueExplicitByColumnAndRow($c, 1, $attr->name, DataType::TYPE_STRING);
                        $c += 1;
                    }
                    $r = 2;
                    /** @var CardBill $bill */
                    foreach ($query->each() as $bill) {
                        if ($bill->package_id != $package->id) {
                            continue;
                        }
                        $sheet->setCellValueExplicitByColumnAndRow(1, $r, $bill->card->iccid, DataType::TYPE_STRING);
                        $sheet->setCellValueExplicitByColumnAndRow(2, $r, $bill->user->username, DataType::TYPE_STRING);
                        $sheet->setCellValueExplicitByColumnAndRow(3, $r, $package->name, DataType::TYPE_STRING);
                        $sheet->setCellValueExplicitByColumnAndRow(4, $r, $userPackage->cost_price, DataType::TYPE_NUMERIC);
                        $sheet->setCellValueExplicitByColumnAndRow(5, $r, $bill->data, DataType::TYPE_NUMERIC);
                        $sheet->setCellValueExplicitByColumnAndRow(6, $r, $bill->sms, DataType::TYPE_NUMERIC);
                        $sheet->setCellValueExplicitByColumnAndRow(7, $r, $bill->voice, DataType::TYPE_NUMERIC);
                        $c = 8;
                        foreach ($attrList as $attr) {
                            $value = CardAttrValue::findOne(['uid' => $bill->user->id, 'card_id' => $bill->card_id, 'attr_id' => $attr->id]);
                            if (!empty($value)) {
                                $sheet->setCellValueExplicitByColumnAndRow($c, $r, $value->value, DataType::TYPE_STRING);
                            }
                            $c += 1;
                        }
                        $r += 1;
                    }
                }
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="card_bill_' . date('Y-m-d') . '.xls"');
                header('Cache-Control: max-age=0');
                $excelWriter = IOFactory::createWriter($excel, 'Xls');
                $excelWriter->save('php://output');
            } catch (Exception $e) {
                Yii::error('导出Excel文件时出错：' . $e->getMessage());
                Yii::error($e->getTraceAsString());
                echo $e->getMessage();
            }
            die();
        }
        $pagination = new Pagination(['totalCount' => $query->count()]);
        $query->orderBy('id DESC');
        $query->offset($pagination->offset)->limit($pagination->limit);
        $billList = $query->all();
        $attrList = empty($user) ? [] : CardAttr::find()->andWhere(['uid' => $user->id])->andWhere(['show_in_list' => 1])->all();

        return $this->render('bill_list', [
            'billList' => $billList,
            'attrList' => $attrList,
            'pagination' => $pagination,
        ]);
    }

    /**
     * 生成账单AJAX接口
     * @return array
     */
    public function actionGenerateBill()
    {
        if (!$this->manager->can('card/bill')) {
            return ['error' => 'SERVER', 'message' => '没有权限。'];
        }

        $month = preg_replace('/\D/', '', $this->get('month'));
        if (empty($month)) {
            return ['error' => 'SERVER', 'message' => '参数错误。'];
        }
        if (CardBill::find()->andWhere(['month' => $month])->exists()) {
            CardBill::deleteAll(['month' => $month]);
            UserBill::deleteAll(['month' => $month]);
        }

        $task = new Task();
        $task->u_type = Task::U_TYPE_MANAGER;
        $task->uid = $this->manager->id;
        $task->name = '生成账单【month=' . $month . '】';
        $task->next = 0;
        $task->todo = json_encode([
            'class' => CardBill::class,
            'method' => 'taskGenerateBill',
            'params' => $month,
        ]);
        $task->status = Task::STATUS_WAITING;
        $task->save();

        return [];
    }
    /**
     * 物联网卡类型
     * @return string
     * @throws ForbiddenHttpException
     */
    public function actionType(){
        if (!$this->manager->can('card/type')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $query = Cardtype::find();
        $query->orderBy('id DESC');
        $cardType = $query->all();
        return $this->render('card-type', [
            'cardType' => $cardType,
        ]);
    }

     /**
     * 物联网卡类型修改
     * @return string
     * @throws ForbiddenHttpException
     */
     public function actionCardtypeEdit(){
        if (!$this->manager->can('card/type')) {
            throw new ForbiddenHttpException('没有权限。');
        }
        $id = $this->get('id');
        if (!empty($id)) {
            $cardtype = Cardtype::findOne(['id' => $id]);
            if (empty($cardtype)) {
                throw new NotFoundHttpException('没有找到此卡类型。');
            }
        } else {
            $cardtype = new Cardtype();

        }
        if ($cardtype->load($this->post()) && $cardtype->save()) {
            ManagerLog::info($this->manager->id, '保存物联网卡类型', print_r($cardtype->attributes, true));
            Yii::$app->session->addFlash('success', '数据已保存。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/type']),
                'txt' => '卡类型列表'
            ]));
        }
        return $this->render('cardtype-edit', [
            'cardtype' => $cardtype,
        ]);
     }
     /**
     * 物联网卡类型删除
     * @return string
     * @throws ForbiddenHttpException
     */
    public function actionCardtypeDel(){
        $id = $this->get('id');
        $model = Cardtype::findOne(['id' => $id]);
        if(empty($model)){
            return [
                'message'=>'没有找到卡类型信息'
            ];
        }
        try {
            $model->delete();
        } catch (\Throwable $e){
        }
        ManagerLog::info($this->manager->id,'删除卡类型',print_r($model->attributes,true));
        $this->redirect(['/admin/card/type']);
    }
    //停机不退费
    public function actionCorrectCardRecharge(){
        $model = new CorrectCardRecharge(); 
        $taocan = Yii::$app->request->post("CorrectCardRecharge");
        if($taocan){
            $str = str_replace(array("\r\n", "\r", "\n"), "", $taocan['iccid_list']);
            $list = strlen($str) / 20;
            $iccidList = [];
            $iccidList = preg_split('/\s/',$taocan['iccid_list'], -1, PREG_SPLIT_NO_EMPTY);
        
        foreach ($iccidList as $iccid) {
            //清除card信息
            $card = Card::find()->andWhere(['like','iccid',$iccid])->one();
            $card->updateAttributes(['package_id'=>'','status'=>card::STATUS_RETIRED,'status_isp'=>card::STATUS_RETIRED,'active_time'=>'','data'=>0,'term_length'=>0,'term_length_unit'=>0,'term_end_time'=>0,'total_flow'=>0,'data_isp'=>0,'order_sn'=>'','dgcs'=>0,'order_time'=>0,'ncid'=>'','open_id'=>'']);
            //根据card_id获取佣金记录
            $agent_profit = AgentProfit::find()->Where(['card_id'=>$card['id']])->all();
            //循环隐藏分成记录
            foreach ($agent_profit as $key => $value) {
                $profit = AgentProfit::findOne(['id'=>$value['id']]);
                $profit->is_show = 0;
                $profit->save();
            }
            $recharge = UserCardRecharge::findOne(['card_id'=>$card['id']]);
            $recharge->is_show = 0;
            $recharge->save();
        }
        }
        if ($model->load($this->post())){
            Yii::$app->session->addFlash('success', '更改完成。');
            Yii::$app->session->setFlash('redirect', json_encode([
                'url' => Url::to(['/admin/card/correct-card-recharge']),
                'txt' => '卡列表'
            ]));
        }
        return $this->render('correct_card_recharge',[
            'model' => $model,
        ]);
    }
    /**
     * 校正卡流量
     */
    public function actionCorrectData(){
        if(!$this->manager->can('card/list')){
            return ['error'=> 'SERVER','message' => '没有权限'];
        }
        $id = $this->get('id');
        $card = Card::findOne(['id'=>$id]);
        if(empty($card)){
            return ['error' => 'NOT FOUND','没有找到卡信息'];
        }
        //获取上个周期的编号
        $ordersn = (new \yii\db\Query())
        ->select(['order_sn'])
        ->from('card_datalog')
        ->where(['card_id'=>$id])
        ->offset(1)
        ->groupBy('order_sn')
        ->orderBy('card_datalog.id desc')
        ->all();
        //获取上个周期第二阶段的用量信息
        $last = CardDataLog::find()->where(['order_sn'=>$ordersn[0]['order_sn']])->orderBy('id desc')->one();
        if(empty($last)){
            return [];
            exit;
        }
        //获取当前周期用量
        $current = CardDataLog::find()->where(['order_sn'=>$card->order_sn])->one();
        if($card->data > 0){
            //把上个周期第二阶段的用量和本周期第一阶段的用量相加
            if($last->data > 0 || $last->data < 0){
                $usage = $last->data + $current->data;
            }else{
                $usage = $last->data;
            }
            //把相加得到的用量保存到上周期第二阶段
            $corrected = CardDatalog::findOne(['id'=>$last->id]);
            $corrected->data = $usage;
            $corrected->save();
            Yii::$app->db->createCommand()->update('card', ['is_correct' => 1], 'id = :id',['id' => $this->get('id')])->execute(); 
            $card->newRefreshBasic();
            return [];
        }else{
            //得到正确的用量
            $real_usage = $last->data + $current->data;
            //把本周期第一阶段的用量保存到上周期第二阶段
            $corrected = CardDatalog::findOne(['id'=>$last->id]);
            $corrected->data = $real_usage;
            $corrected->save();
            Yii::$app->db->createCommand()->update('card', ['is_correct' => 1], 'id = :id',['id' => $this->get('id')])->execute(); 
            $card->newRefreshBasic();
            return [];
        }
        

    }
    /**
     * 增加卡流量
     */
    public function actionAddFlow(){
        $card = Card::findOne(['id'=>$this->get('id')]);
        if($this->get('flow')){
            $sub = substr($this->get('flow'), -1);
            if ($sub == 'G') {
                $str = str_replace('G', '', $this->get('flow'));
                $flow = $str * 1024*1024*1024;
            }
            if ($sub == 'M') {
                $str = str_replace('M', '', $this->get('flow'));
                $flow = $str * 1024*1024;
            }
            $card->total_flow = $card->total_flow + $flow;
            $card->save();
            ManagerLog::info($this->manager->id, '增加卡流量', print_r($this->get(), true));
            return [];
        }
        
    }
}
